from openpyxl import load_workbook
from openpyxl.styles import Alignment, PatternFill


class KenuoStyle:
    def __init__(self, filename):
        self.wb = load_workbook(filename)
        self.filename = filename

    def style_modify(self):
        wb = self.wb
        for i in wb.sheetnames:
            ws = wb[i]
            # 给第一行添加灰色背景色
            for cell in ws[1]:
                cell.fill = PatternFill(start_color='C0C0C0', end_color='C0C0C0', fill_type='solid')
            # 给所有单元格加上居中样式
            cell_alignment = Alignment(horizontal='center', vertical='center')
            for row in ws.rows:
                for cell in row:
                    cell.alignment = cell_alignment
            # 给第一行添加行高
            ws.row_dimensions[1].height = 30
            try:
                # 单独给货收地址添加居左样式
                col_letter = self.col_letter(sheet_name=i, col_name='货收地址')
                for cell in ws[col_letter]:
                    cell.alignment = Alignment(horizontal='left')
                # 标题居中样式保留
                str_col_letter = f'{col_letter}1'
                ws[str_col_letter].alignment = Alignment(horizontal='center', vertical='center')
                # 设置每列的宽度
                self.col_max_length(sheet_name=i, col_name='货收地址')
                self.col_max_length(sheet_name=i, col_name='货收联系人')
                self.col_max_length(sheet_name=i, col_name='收货联系电话')
                self.col_max_length(sheet_name=i, col_name='冷链件数')
                self.col_max_length(sheet_name=i, col_name='脆筒')
                self.col_max_length(sheet_name=i, col_name='设备')
                self.col_max_length(sheet_name=i, col_name='备注')
            except TypeError as e:
                print('专线没有其他城市数据')
        wb.save(self.filename)

    def col_letter(self, sheet_name, col_name):
        '''用于按照单元格的值返回列编号
        :param sheet_name:
        :param col_name:
        :return: col_letter
        '''
        ws = self.wb[sheet_name]
        for cell in ws[1]:
            if cell.value == col_name:
                return cell.column_letter

    def col_max_length(self, sheet_name, col_name):
        '''根据列名获取该列最长的值,按照字数编辑列的长度
        :param sheet_name:
        :param col_name:
        :return:
        '''
        ws = self.wb[sheet_name]
        # col_name的列号，定义变量
        col_name_num = 0
        col_name_letter = ''
        # ws[1]即表单第一行所有单元格对象
        for cell in ws[1]:
            if cell.value == col_name:
                # 返回货收地址的列号，int
                col_name_num = cell.column
                col_name_letter = cell.column_letter
        # 定义列表，存放col的所有参数
        cell_value_list = []
        for row in range(1, ws.max_row + 1):
            cell_value = ws.cell(row=row, column=col_name_num).value
            # 将该列的所有单元格的值，添加入列表
            cell_value_list.append(str(cell_value))
        # 返回最长的值的长度,int
        col_width = len(max(cell_value_list, key=len)) * 2
        # 打印最长的值
        print(max(cell_value_list, key=len))
        ws.column_dimensions[col_name_letter].width = col_width
        # 打印列宽
        print(col_width)


if __name__ == '__main__':
    ks = KenuoStyle(filename=r"D:\pythonProject1\keNuoProject\2023-04-03\2023-04-03直送.xlsx")
    ks.style_modify()
